#include "databasectl.h"
#include <stdio.h>
#include <string.h>
static char prename[100] = "./my.db";



int verify_user(const char *username, const char *passwd)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[100]={0};
	sprintf(buf,"select * from userpasswd where name=\"%s\" AND passwd=\"%s\"",username,passwd);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	
	index = ncolumn;
	for(i = 0; i < nrow; i++)
	{
		for(j = 0; j < ncolumn; j++)
			printf("%s:%s\n",resultp[j],resultp[index++]);
	}
	printf("row:%d,column:%d\n",nrow, ncolumn);

	sqlite3_free_table(resultp);

	sqlite3_close(db);

	if(nrow > 0)
		return 1;
	else
		return 0;


}
int user_isexist(const char *username)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[100]={0};
	sprintf(buf,"select * from userpasswd where name=\"%s\"",username);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	
	index = ncolumn;
	for(i = 0; i < nrow; i++)
	{
		for(j = 0; j < ncolumn; j++)
			printf("%s:%s\n",resultp[j],resultp[index++]);
	}
	printf("row:%d,column:%d\n",nrow, ncolumn);

	sqlite3_free_table(resultp);

	sqlite3_close(db);
	if(nrow > 0)
		return 1;
	else
		return 0;

}
int add_user(const struct mem_info *mem)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[200]={0};
	sprintf(buf,"insert into userpasswd values(\"%d\",\"%s\", \"%s\",\"%s\", \"%s\", \"%d\", \"%d\", \"%d\",\"%d\" )",mem->mem_no,mem->name,mem->passwd,mem->address, mem->phone, mem->permission, mem->age,mem->msg_flag, mem->salary);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}

	sqlite3_free_table(resultp);

	sqlite3_close(db);
	return 1;

}

int del_user(const struct mem_info *mem)
{

	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[200]={0};
	sprintf(buf,"delete from userpasswd where name = \"%s\"",mem->name);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	sqlite3_free_table(resultp);

	sqlite3_close(db);
	return 1;

}

int select_table_byname(const char *username, struct mem_info *mem)
{
	
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[200]={0};
	sprintf(buf,"select * from userpasswd where name = \"%s\"",username);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}	
	if(nrow == 0)
	{
		return 0;
	}
	mem->mem_no = atoi(resultp[9]);
	strcpy(mem->name,resultp[10]);
	strcpy(mem->passwd,resultp[11]);
	strcpy(mem->address, resultp[12]);
	strcpy(mem->phone, resultp[13]);
	mem->permission = atoi(resultp[14]);
	mem->age = atoi(resultp[15]);
	mem->msg_flag = atoi(resultp[16]);
	mem->salary = atoi(resultp[17]);

	sqlite3_free_table(resultp);

	sqlite3_close(db);
	return 1;

}
int modify_table_byname(const char *username, char *column_name, char *column_value)
{

	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[200]={0};
	sprintf(buf,"update userpasswd set %s = %s where name = \"%s\"",column_name, column_value,username);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	sqlite3_free_table(resultp);

	sqlite3_close(db);
	return 1;

}
int get_all_name(char **rbuf)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[200]={0};
	sprintf(buf,"select name from userpasswd");
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	*rbuf = (char *)malloc(nrow * 20 + 50);
	**rbuf = 0;
	index = ncolumn;
	for(i = 0; i < nrow; i++)
	{
		strcat(strcat(*rbuf,resultp[index++]),"  ");
		
	}


	sqlite3_free_table(resultp);

	sqlite3_close(db);
	return 1;
	
}

void free_all_name(char *buf)
{
	free(buf);	
}

int get_all_msg(char **rbuf)
{

	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[200]={0};
	sprintf(buf,"select * from userpasswd");
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	*rbuf = (char *)malloc(nrow * 100 + 50);
	**rbuf = 0;
	index = 0;

	char wbuf[50];
	for(i = 0; i <= nrow; i++)
	{
		for(j = 0; j < ncolumn; j++)
		{
			sprintf(wbuf, "%s  ", resultp[index++]);
			strcat(*rbuf,wbuf);
		}
			strcat(*rbuf, "\n");
	}


	sqlite3_free_table(resultp);

	sqlite3_close(db);
	return 1;
}

void free_all_msg(char *buf)
{
	free(buf);
}














/*
int create_filetable(const char *username)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[100]={0};
	sprintf(buf,"create table %s(filename,filesize,filepath)",username);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	return 0;
	
}

*/
static int create_passwdtable(const char *tablename)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[100]={0};
	sprintf(buf,"create table %s(mem_no int,name varchar(20),passwd varchar(20),address varchar(50),phone varchar(15), permission int, age int, msg_flag int,salary int)",tablename);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}

	sqlite3_free_table(resultp);

	sqlite3_close(db);

}



/*
int file_isexist(const char *username, const char *filename)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[100]={0};
	sprintf(buf,"select * from %s where filename=\"%s\"",username,filename);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	
	index = ncolumn;
	for(i = 0; i < nrow; i++)
	{
		for(j = 0; j < ncolumn; j++)
			printf("%s:%s\n",resultp[j],resultp[index++]);
	}
	printf("row:%d,column:%d\n",nrow, ncolumn);

	if(nrow > 0)
		return 1;
	else
		return 0;



}

*/

/*
int add_filename(const char *username, const char *filename, const int filesize)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[100]={0};
	sprintf(buf,"insert into %s values(\"%s\",\"%d\")",username,filename,filesize);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	return 0;

}

int get_files(const char *username, char *rbuf)
{
	sqlite3 *db;
	if(sqlite3_open(prename,&db) != SQLITE_OK)
	{
		printf("error:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	char buf[100]={0};
	rbuf[0] = 0;
	sprintf(buf,"select filename from %s",username);
	printf("%s\n",buf);
	char *errmsg, **resultp;
	int nrow, ncolumn,i,j,index;
	if(sqlite3_get_table(db, buf, &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
	{
		printf("error:%s\n",errmsg);
		return -2;
	}
	index = ncolumn;
	for(i = 0; i < nrow; i++)
	{
		for(j = 0; j < ncolumn; j++)
		{
			strcat(strcat(rbuf,resultp[index++]),"  ");
	//		sprintf(rbuf,"%s ",resultp[index++]);
			printf("%s\n",resultp[index-1]);
		}
	}
	strcat(rbuf,"\n");
	return 0;

}

int check_file(const char *filename, char *srcfilename)
{
	
}

*/
